﻿# -*- coding:utf-8 -*-
from django.db import models#, backend
#from django.contrib.auth.models import User

class AppartmentSummManager(models.Manager):
    use_for_related_fields = True
    def __init__(self):
        super(AppartmentSummManager, self).__init__()
        
    def MonthSumm(self, month, year):
        from django.db import connection
        cursor = connection.cursor()
        cursor.execute("""
            SELECT 
                `house_monthlypayment`.`id`, 
                `house_monthlypayment`.`apartment_id`, 
                `house_monthlypayment`.`payment_id`, 
                `house_monthlypayment`.`year`, 
                `house_monthlypayment`.`month`, 
                `house_monthlypayment`.`amount`, 
                `house_monthlypayment`.`data`, 
                `house_apartment`.`summ`,
            SUM(`house_monthlypayment`.`amount`) AS `month_summ`, 
            MAX(`house_monthlypayment`.`data`) AS `last_data` 
            FROM `house_monthlypayment`  
            LEFT OUTER JOIN `house_apartment`  
            ON (`house_monthlypayment`.`apartment_id` = `house_apartment`.`id`) 
            WHERE (`house_monthlypayment`.`year` = '%s' AND `house_monthlypayment`.`month` = '%s' ) 
            GROUP BY `apartment_id` 
            ORDER BY `house_monthlypayment`.`apartment_id` ASC""", [year, month] )
        result_list = []
        for row in cursor.fetchall():
            #p = self.model( id=row[0], 
            p = {'id':row[0],
                            'apartment_id':row[1],
                            'payment_id':row[2],
                            #year
                            #month
                            #amount
                            #data
                            'summ':row[7],
                            'month_summ':row[8],
                            'last_data':row[9],
                            }
            #p.num_responses = row[3]
            result_list.append(p)
        return result_list


